
-- Project: SQL Student/Shops Database
-- Description:
	-- This is the students and shops database, which has 2 tables.
	-- One of the tables has the students and the second table has to shops, which shows the connection of the students in their shops.
	-- This database also used the command join to use both databases to find information.
	-- These tables are connected through the shopId, which is used to find the student shop's.
-- Jamila Kandawala
-- Date: 2/7/2025


create database studentShops; -- This is creating a database called studentShops, which can make tables inside of the database.

use studentShops; -- This is allowing me to use the database and it will allow me to create tables inside the table after this statement.

-- This is creating a table called students with 4 fields inside of it, which are the primary key, fname, lname, and shopId.
create table students(
	id int primary key auto_increment, -- This will make a primary key, which will be made auto-increment that means it has no value.
	fname varchar(20), -- This is the fname, which is the first name of each student and it has a character limit of 20.
	lname varchar(20), -- This is the lname, which is the last name of each student and it has a character limit of 20.
	shopId INT -- This is the shopId, which connects the two tables, and it is an integer so it has a value of numbers.
);

-- This is creating a table that is called shops, which has 2 fields and it is the shopId, which is the primary key, and the shop for each students.
create table shops(
	shopId INT Primary Key, -- This is the shop id, so it will be the primary key and it will connect all the values and give its number to it.
	shop varchar(5) -- This is the shop for each of the students and it is only the abbreviation, so it has a character limit of 5.
);


describe students; -- This is describing the students table, which allows the columns to be finalized and it is complete to allow data to be put in it.

describe shops; -- This is describing the shop table, which has the 2 fields and columns, so it is completed to put values in each. 


insert into students -- This is inserting data into the students table 
(fname, lname, shopId) -- This is the columns or fields for the table of students, which will be the format of the data.
values -- This has all the data and the values below with the format above.
('Mary', 'Hayes', 12), -- This is the data for Mary Hayes. 
('Kylan', 'Jones', 5), -- This is the for Kylan Jones.
('Andrew', 'Roberts', 3),  -- This is the data for Andrew Roberts.
('Lilyana', 'Carson', 2), -- This is the data for Lilyana Carson.
('Caidan', 'Mays', 7), -- This is the data for Caidan Mays.
('Stella', 'Walker', 7), -- This is the data for Stella Walker.
('Trenton', 'Allen', 11), -- This is the data for Trenton Allen.
('Ben', 'Pearson', 4), -- This is the data for Ben Pearson.
('Ares', 'Jacobs', 10), -- This is the data for Ares Jacods.
('Ramon', 'Scott', 8), -- This is the data for Ramon Scott.
('Aila', 'Sanchez', 2), -- This is the data for Aila Sanchez.
('Bryant', 'Cardenas', 1), -- This is the data for Bryant Cardenas. 
('Cooper', 'Vazquez', 10), -- This is the data for Cooper Vazquez.
('Ellis', 'McKinney', 6), -- This is the data for Ellis McKinney.
('Ella', 'Mason', 9), -- This is the data for Ella Mason.
('Jordan', 'Wilcox', 2), -- This is the data for Jordon Wilcox.
('Alexander', 'Smith', 5), -- This is the data for Alexander Smith.
('Destiny', 'Morrow', 9), -- This is the data for Destiny Morrow.
('Henry', 'Hoover', 2), -- This is the data for Henry Hoover.
('Martha', 'Rivera', 1) -- This is the data for Martha Rivera. 
;

insert into shops -- This is showing that the values below will be inserted into the shops table.
(shopId, shop) -- These are the two fields from the shops table, which are the id and the shop name.
values  -- All the data below is the values that will be inputted into the table. 
(1, 'AT'), -- The first shop is Automotive Technology.
(2, 'AU'), -- The second shop is Automotive Collison.
(3, 'AR'), -- The third shop is Architecture. 
(4, 'CA'), -- The fourth shop is Carpentry.
(5, 'CU'), -- The sixth shop is Culinary.
(6, 'EL'), -- The seventh shop is Electrical.
(7, 'HC'), -- The eighth shop is Hairdressing and Cosmetology.
(8, 'HVAC'), -- The ninth shop is Heating, Ventilation, and Air Conditioning.
(9, 'IT'), -- The tenth shop is Information Technology.
(10, 'MT'), -- The eleventh shop is Precision Machining
(11, 'PH'), -- The twelfth is Plumbing and Heating. 
(12, 'RA') -- The thirteenth is Robotics and Automation.
;


Select students.fname as 'First Name', students.lname as 'Last Name', shops.shop as 'Shop' -- This is selecting the student field first name and last name and the shops name to display the information.
From students join shops -- This is joining the two tables together, which are the students and shops tables.
Where students.shopId = shops.shopId AND shops.shopId = '2'; -- This is making the students shopId be equal to the shops shopId and then it it is going into the shops table and making the shop that is automotive collison be displayed with all the student names.

Select students.fname as 'First Name', students.lname as 'Last Name', shops.shop as 'Shop' -- This is selecting the student field first name and last name and the shops name to display the information.
From students join shops -- This is joining the two tables together, which are the students and shops tables.
Where students.shopId = shops.shopId AND shops.shop = 'IT'; -- This is making the both shopId from the tables be equal and the students with the shop IT be displayed. 

Select students.fname as 'First Name', students.lname as 'Last Name', shops.shop as 'Shop' -- This is selecting the student field first name and last name and the shops name to display the information.
From students join shops -- This is joining the two tables together, which are the students and shops tables.
Where students.shopId = shops.shopId AND students.lname = 'Hoover'; -- This is making the shopId's from both of the tables be equal to each other and then it is displaying all the student last names that are Hoover.

drop database studentShops; -- This will delete the database called studentShops, which will also delete the two tables inside of it called shops and students.

